﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using crmbl.Azure.Globals;

namespace crmbl.Azure.Metadata.Globals
{
    public class ApplicationHelper : IDisposable
    {
        SqlHelper _sql;

        DataTable _performanceCounterCache;

        DataTable _roleCache;

        DataTable _eventLogCache;
        DataTable _eventSourceCache;

        DataTable _infaFunction;

        #region Constructor

        public ApplicationHelper(String appConfigConnectionString)
        {
            _sql = new SqlHelper(appConfigConnectionString);

            CreateTablesIfDoesNotExist();
        }

        #endregion


        public void RunDataAggregations()
        {
            _sql.RunQueryGetScalar("[dbo].[usp_RunAggregations]",null,true);
        }

        public void CreateTablesIfDoesNotExist()
        {
            try
            {
                if (_sql.RunQueryGetDataTable("SELECT * FROM sys.sysobjects WHERE name = 'StorageAccount' and xtype = 'U'").Rows.Count == 0)
                {
                    Logger.WriteMessage("The referenced database does not contain application tables, tables will be created.", EventLevel.Information, 2010);

                    try
                    {
                        //TODO: Create all tables
                        String createObjectsString = @"

CREATE TABLE [dbo].[StorageAccount](
	[StorageAccountId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[StorageAccountName] [varchar](150) NOT NULL,
	[StorageAccountKey] [varchar](500) NOT NULL,
	[IsActive] [int] NOT NULL,
    CONSTRAINT [PK_StorageAccount] PRIMARY KEY CLUSTERED 
(
	[StorageAccountId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[StorageAccount] ADD  CONSTRAINT [DF_StorageAccount_StorageAccountId]  DEFAULT (newid()) FOR [StorageAccountId]
GO

ALTER TABLE [dbo].[StorageAccount] ADD  CONSTRAINT [DF_StorageAccount_IsActive]  DEFAULT ((1)) FOR [IsActive]
GO

CREATE TABLE [dbo].[WindowsEventEntry](
	[WindowsEventEntryRowId] [int] IDENTITY(1,1) NOT NULL,
	[RoleRowId] [int] NOT NULL,
	[WindowsEventLogRowId] [int] NOT NULL,
	[WindowsEventSourceRowId] [int] NOT NULL,
	[DateTimeUTC] [datetime] NOT NULL,
	[EventNumber] [int] NOT NULL,
	[InstanceNumber] [smallint] NOT NULL,
	[EventLevel] [smallint] NOT NULL,
	[Description] [varchar](max) NOT NULL,
 CONSTRAINT [PK_WindowsEventEntry] PRIMARY KEY CLUSTERED 
(
	[WindowsEventEntryRowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[WindowsEventLog](
	[WindowsEventLogRowId] [int] IDENTITY(1,1) NOT NULL,
	[EventLogName] [varchar](250) NOT NULL,
 CONSTRAINT [PK_WindowsEventLog] PRIMARY KEY CLUSTERED 
(
	[WindowsEventLogRowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[WindowsEventSource](
	[WindowsEventSourceRowId] [int] IDENTITY(1,1) NOT NULL,
	[EventSourceName] [varchar](150) NOT NULL,
 CONSTRAINT [PK_WindowsEventSource] PRIMARY KEY CLUSTERED 
(
	[WindowsEventSourceRowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[TableDownload](
	[TableDownloadId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[StorageAccountId] [uniqueidentifier] NOT NULL,
	[TableName] [varchar](150) NOT NULL,
	[LastProcessedPartitionKey] [varchar](500) NOT NULL,
	[LastProcessedRowKey] [varchar](500) NOT NULL,
	[LastProcessedTimestampUTC] [datetime] NOT NULL,
	[DateTimeLastUpdatedUTC] [datetime] NOT NULL,
 CONSTRAINT [PK_TableDownload] PRIMARY KEY CLUSTERED 
(
	[TableDownloadId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[TableDownload] ADD  CONSTRAINT [DF_TableDownload_TableDownloadId]  DEFAULT (newid()) FOR [TableDownloadId]
GO

ALTER TABLE [dbo].[TableDownload] ADD  CONSTRAINT [DF_TableDownload_LastProcessedTimestamp]  DEFAULT ((0)) FOR [LastProcessedTimestampUTC]
GO

CREATE TABLE [dbo].[Role](
	[RoleRowId] [int] IDENTITY(1,1) NOT NULL,
	[DeploymentId] [uniqueidentifier] NOT NULL,
	[RoleName] [varchar](255) NOT NULL,
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED 
(
	[RoleRowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[PerformanceSample](
	[PerformanceSampleRowId] [int] IDENTITY(1,1) NOT NULL,
	[PerformanceCounterRowId] [smallint] NOT NULL,
	[RoleRowId] [smallint] NOT NULL,
	[DateTimeUTC] [smalldatetime] NOT NULL,
	[InstanceNumber] [smallint] NOT NULL,
	[SampleValue] [float] NOT NULL,
 CONSTRAINT [PK_PerformanceSample] PRIMARY KEY CLUSTERED 
(
	[PerformanceSampleRowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[PerformanceHourly](
	[PerformanceHourlyRowId] [int] IDENTITY(1,1) NOT NULL,
	[RoleRowId] [smallint] NOT NULL,
	[PerformanceCounterRowId] [smallint] NOT NULL,
	[InstanceNumber] [smallint] NOT NULL,
	[DateTimeUTC] [smalldatetime] NOT NULL,
	[MinValue] [float] NOT NULL,
	[MaxValue] [float] NOT NULL,
	[AverageValue] [float] NOT NULL,
	[StdDev] [float] NOT NULL,
 CONSTRAINT [PK_PerformanceHourly] PRIMARY KEY CLUSTERED 
(
	[PerformanceHourlyRowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[PerformanceCounter](
	[PerformanceCounterRowId] [int] IDENTITY(1,1) NOT NULL,
	[ObjectName] [varchar](150) NOT NULL,
	[CounterName] [varchar](150) NOT NULL,
	[InstanceName] [varchar](150) NOT NULL,
	[FullCounterString] [varchar](500) NOT NULL,
 CONSTRAINT [PK_PerformanceCounter] PRIMARY KEY CLUSTERED 
(
	[PerformanceCounterRowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[LogEvent](
	[LogEventRowId] [int] IDENTITY(1,1) NOT NULL,
	[RoleRowId] [smallint] NOT NULL,
	[DateTimeUTC] [datetime] NOT NULL,
	[EventNumber] [smallint] NOT NULL,
	[EventLevel] [tinyint] NOT NULL,
	[InstanceNumber] [smallint] NOT NULL,
	[Message] [varchar](max) NOT NULL,
 CONSTRAINT [PK_LogEvent] PRIMARY KEY CLUSTERED 
(
	[LogEventRowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[EventLevel](
	[EventLevelId] [tinyint] IDENTITY(1,1) NOT NULL,
	[EventLevelName] [varchar](25) NOT NULL,
 CONSTRAINT [PK_EventLevel] PRIMARY KEY CLUSTERED 
(
	[EventLevelId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Deployment](
	[DeploymentId] [uniqueidentifier] NOT NULL,
	[HostedServiceId] [uniqueidentifier] NOT NULL,
	[Name] [varchar](150) NOT NULL,
 CONSTRAINT [PK_Deployment] PRIMARY KEY CLUSTERED 
(
	[DeploymentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-- STORED PROCEDURES


CREATE PROC [dbo].[usp_PerformanceHourly_Build]
AS

INSERT INTO [dbo].[PerformanceHourly] (RoleRowId, PerformanceCounterRowId, InstanceNumber, DateTimeUTC, MinValue, MaxValue, AverageValue, StdDev)
SELECT A.RoleRowId, A.PerformanceCounterRowId, A.InstanceNumber, A.DateTimeUTC, A.MinValue, A.MaxValue, A.AverageValue, IsNull(A.StdDev, 0) AS StdDev
FROM [vw_PerformanceSample_Hourly] A
LEFT OUTER JOIN 
		(
			-- Get the latest
			SELECT MAX(A.DateTimeUTC) As DateTimeUTC, A.RoleRowId, A.InstanceNumber, A.PerformanceCounterRowId
			FROM [vw_PerformanceSample_Hourly] A
			GROUP BY A.RoleRowId, A.InstanceNumber, A.PerformanceCounterRowId
		) AS B
		ON A.DateTimeUTC = B.DateTimeUTC
			AND A.RoleRowId = B.RoleRowId
			AND A.InstanceNumber = B.InstanceNumber
			AND A.PerformanceCounterRowId = B.PerformanceCounterRowId
	LEFT OUTER JOIN PerformanceHourly C
		ON A.DateTimeUTC = C.DateTimeUTC
			AND A.RoleRowId = C.RoleRowId
			AND A.InstanceNumber = C.InstanceNumber
			AND A.PerformanceCounterRowId = C.PerformanceCounterRowId
WHERE B.DateTimeUTC is null
	AND C.PerformanceHourlyRowId is null

GO

CREATE PROC [dbo].[usp_RunAggregations]
AS

exec [dbo].[usp_PerformanceHourly_Build]

GO


-- VIEWS

CREATE VIEW [dbo].[vw_LogEvent_Detailed]
AS
SELECT A.LogEventRowId, B.DeploymentId, A.RoleRowId, B.RoleName, InstanceNumber, DateTimeUTC, EventNumber, EventLevel, EventLevelName, [Message]
FROM LogEvent A
	LEFT OUTER Join [Role] B
		On A.RoleRowId = B.RoleRowId
	LEFT OUTER Join EventLevel C
		On A.EventLevel = C.EventLevelId
GO

CREATE VIEW [dbo].[vw_PerformanceSample_Detailed]
AS

Select A.PerformanceSampleRowId, C.DeploymentId, C.RoleRowId, C.RoleName, B.PerformanceCounterRowId, B.ObjectName, B.CounterName, B.InstanceName, A.InstanceNumber, A.DateTimeUTC, A.SampleValue
From PerformanceSample A with (nolock)
	Join PerformanceCounter B with (nolock)
		On A.PerformanceCounterRowId = B.PerformanceCounterRowId
	Join [Role] C with (nolock)
		On A.RoleRowId = C.RoleRowId
GO

CREATE VIEW [dbo].[vw_PerformanceSample_Hourly]
AS

SELECT CAST(DATEADD(MINUTE,-1*DATEPART(MINUTE,MaxDateTimeUTC),MaxDateTimeUTC) As smalldatetime) As DateTimeUTC, *
FROM
	(
	SELECT 
		DATEPART(YEAR,DateTimeUTC) AS DateTimeUTCYear, 
		DATEPART(MONTH,DateTimeUTC) AS DateTimeUTCMonth,
		DATEPART(DAY,DateTimeUTC) AS DateTimeUTCDay,
		DATEPART(HOUR,DateTimeUTC) AS DateTimeUTCHour,
		MAX(DateTimeUTC) As MaxDateTimeUTC,
		A.RoleRowId,
		A.InstanceNumber,
		A.PerformanceCounterRowId,
		AVG(A.SampleValue) AS AverageValue,
		MIN(A.SampleValue) AS MinValue,
		MAX(A.SampleValue) AS MaxValue,
		STDEV(A.SampleValue) As StdDev
	FROM [dbo].[PerformanceSample] A
	GROUP BY A.RoleRowId, A.InstanceNumber, A.PerformanceCounterRowId, DATEPART(YEAR,DateTimeUTC), DATEPART(MONTH,DateTimeUTC), DATEPART(DAY,DateTimeUTC), DATEPART(HOUR,DateTimeUTC)
	) AS A

GO


CREATE VIEW [dbo].[vw_WindowsEventEntry_Detailed]
AS 

Select A.WindowsEventEntryRowId, A.RoleRowId, B.RoleName, A.InstanceNumber, B.DeploymentId, A.WindowsEventLogRowId, E.EventLogName, A.WindowsEventSourceRowId, C.EventSourceName, DateTimeUTC, A.EventLevel, D.EventLevelName, A.Description
From [dbo].[WindowsEventEntry] A
	Left Outer Join [dbo].[Role] B
		On A.RoleRowId = B.RoleRowId
	Left Outer Join [dbo].[WindowsEventSource] C
		On C.WindowsEventSourceRowId = A.WindowsEventSourceRowId
	Left Outer Join [dbo].[EventLevel] D
		On A.EventLevel = D.EventLevelId
	Left Outer Join [dbo].[WindowsEventLog] E
		On E.WindowsEventLogRowId = A.WindowsEventLogRowId
GO

CREATE VIEW [dbo].[vw_LogEntry]
AS

SELECT DeploymentId, RoleRowId, RoleName, InstanceNumber, DateTimeUTC,  EventLogName As LogName, EventSourceName As LogSource, EventLevel, [Description] AS [Mesage], 'WindowsEvent' As LogType
FROM [dbo].[vw_WindowsEventEntry_Detailed]

UNION ALL

Select DeploymentId, RoleRowId, RoleName, InstanceNumber, DateTimeUTC, 'ETW' As LogName, '' As LogSource, EventLevel, [Message], 'ETW' As LogType
From [dbo].[vw_LogEvent_Detailed]

GO


                            ";

                        _sql.RunBatchScript(createObjectsString);
                    }
                    catch (Exception ex)
                    {
                        Logger.WriteMessage(String.Format("Failed to create application tables. Retry will be attempted at next loop interval. EXCEPTION: {0}", ex.ToString()), EventLevel.Error, 2012);
                    }
                }

            }
            catch (Exception ex)
            {
                throw new Exception(String.Format("Failed to view if application tables exist.  EXCEPTION: {0}", ex.ToString()));
            }
        }


        #region Storage Account

        public List<StorageAccount> GetStorageAccounts(Boolean showInactive = false)
        {
            List<StorageAccount> accounts = new List<StorageAccount>();

            foreach(DataRow r in _sql.RunQueryGetDataTable("SELECT * FROM [StorageAccount] " + (showInactive ? "" : " WHERE IsActive = 1")).Rows)
            {
                StorageAccount a = new StorageAccount();
                a.StorageAccountId = (Guid)r["StorageAccountId"];
                a.StorageAccountName = r["StorageAccountName"].ToString();
                a.StorageAccountKey = r["StorageAccountKey"].ToString();
                a.IsActive = (r["IsActive"].ToString() == "1");

                accounts.Add(a);
            }

            return accounts;
        }

        #endregion


        #region Event Log

        private void PopulateCacheTableEvent()
        {
            _eventLogCache = _sql.RunQueryGetDataTable("SELECT * From [WindowsEventLog]");
        }

        private void PopulateCacheTableSource()
        {
            _eventSourceCache = _sql.RunQueryGetDataTable("SELECT * FROM [WindowsEventSource]");
        }

        public int GetWindowsEventLogId(String eventLogName)
        {
            // Populate the cache tables
            if (_eventLogCache == null)
                PopulateCacheTableEvent();

            // Find if the value is in the cache table
            var x = from a in _eventLogCache.AsEnumerable()
                    where a.Field<string>("EventLogName") == eventLogName
                    select a;

            if (x.Count() == 0)
            {
                // Insert a new Entry
                _sql.RunQueryGetRowsAffected("INSERT INTO WindowsEventLog (EventLogName) VALUES (@EventLogName)",
                    SqlHelper.GenerateParameterDictionary("@EventLogName", eventLogName));

                // Re-populate Cache
                PopulateCacheTableEvent();

                // Recurse this bitch
                return GetWindowsEventLogId(eventLogName);
            }
            else
            {
                return x.First().Field<int>("WindowsEventLogRowId");
            }
        }


        public int GetWindowsEventSourceId(String eventSource)
        {
            // Populate the cache tables
            if (_eventSourceCache == null)
                PopulateCacheTableSource();

            // Find if the value is in the cache table
            var x = from a in _eventSourceCache.AsEnumerable()
                    where a.Field<string>("EventSourceName") == eventSource
                    select a;

            if (x.Count() == 0)
            {
                // Insert a new Entry
                _sql.RunQueryGetRowsAffected("INSERT INTO WindowsEventSource (EventSourceName) VALUES (@EventSourceName)",
                    SqlHelper.GenerateParameterDictionary("@EventSourceName", eventSource));

                // Re-populate Cache
                PopulateCacheTableSource();

                // Recurse this bitch
                return GetWindowsEventSourceId(eventSource);
            }
            else
            {
                return x.First().Field<int>("WindowsEventSourceRowId");
            }
        }

        public void WriteEventLogEntryBulk(DataTable data)
        {
            _sql.RunBulkInsert("WindowsEventEntry", data, true);
        }

        public void WriteEventLogEntry(WindowsEventLogEntity eventEntry)
        {
            Dictionary<String, Object> d = new Dictionary<string, object>();

            d.Add("@RoleRowId", GetRoleRowId(Guid.Parse(eventEntry.DeploymentId),eventEntry.Role));
            d.Add("@WindowsEventLogRowId", GetWindowsEventLogId(eventEntry.Channel));
            d.Add("@WindowsEventSourceRowId", GetWindowsEventSourceId(eventEntry.ProviderName));
            d.Add("@DateTimeUTC", new DateTime(eventEntry.EventTickCount));
            d.Add("@EventNumber", eventEntry.EventId);
            d.Add("@InstanceNumber", GetInstanceNumberFromFullName(eventEntry.RoleInstance));
            d.Add("@EventLevel", eventEntry.Level);
            d.Add("@Description", eventEntry.Description);

            _sql.RunQueryGetRowsAffected("INSERT INTO [WindowsEventEntry] (RoleRowId, WindowsEventLogRowId, WindowsEventSourceRowId, DateTimeUTC, EventNumber, InstanceNumber, EventLevel, [Description]) VALUES (@RoleRowId, @WindowsEventLogRowId, @WindowsEventSourceRowId, @DateTimeUTC, @EventNumber, @InstanceNumber, @EventLevel, @Description)", d);
        }

        #endregion

        #region ETW Logs

        public void WriteLogEntryBulk(DataTable data)
        {
            _sql.RunBulkInsert("LogEvent", data, true);
        }


        public void WriteLogEntry(LogEntity eventEntry)
        {
            Dictionary<String, Object> d = new Dictionary<string, object>();

            d.Add("@RoleRowId", GetRoleRowId(Guid.Parse(eventEntry.DeploymentId), eventEntry.Role));
            d.Add("@DateTimeUTC", new DateTime(eventEntry.EventTickCount));
            d.Add("@EventNumber", eventEntry.EventId);
            d.Add("@EventLevel", eventEntry.Level);
            d.Add("@InstanceNumber", GetInstanceNumberFromFullName(eventEntry.RoleInstance));
            d.Add("@Message", eventEntry.Message);
            
            _sql.RunQueryGetRowsAffected("INSERT INTO [LogEvent] (RoleRowId, DateTimeUTC, EventNumber, EventLevel, InstanceNumber, [Message]) VALUES (@RoleRowId, @DateTimeUTC, @EventNumber, @EventLevel, @InstanceNumber, @Message)", d);
        }

        #endregion


        #region Infrastructure Logs

        private void PopulateCacheTableInfraFunction()
        {
            _infaFunction = _sql.RunQueryGetDataTable("SELECT * From [dbo].[DiagnosticsInfrastructureFunction]");
        }

        public void WriteInfrastructureLogEntryBulk(DataTable data)
        {
            _sql.RunBulkInsert("DiagnosticsInfrastructureLogEntry", data, true);
        }

        public void WriteInfrastructureLogEntry(InfrastructureLogEntry eventEntry)
        {
            Dictionary<String, Object> d = new Dictionary<string, object>();

            d.Add("@RoleRowId", GetRoleRowId(Guid.Parse(eventEntry.DeploymentId), eventEntry.Role));
            d.Add("@DiagnosticsInfrastructureFunctionRowId", GetInfrastructureFunctionRowId(eventEntry.Function));
            d.Add("@DateTimeUTC", new DateTime(eventEntry.EventTickCount));
            d.Add("@Line", eventEntry.Line);
            d.Add("@MDRESULT", eventEntry.MDRESULT);
            d.Add("@ErrorCode", eventEntry.ErrorCode);
            d.Add("@ErrorCodeMessage", eventEntry.ErrorCodeMsg);
            d.Add("@InstanceNumber", GetInstanceNumberFromFullName(eventEntry.RoleInstance));
            d.Add("@EventLevel", eventEntry.Level);
            d.Add("@Message", eventEntry.Message);

            _sql.RunQueryGetRowsAffected("INSERT INTO [DiagnosticsInfrastructureLogEntry] (RoleRowId, DiagnosticsInfrastructureFunctionRowId, DateTimeUTC, Line, MDRESULT, ErrorCode, ErrorCodeMessage, InstanceNumber, EventLevel, [Message]) VALUES (@RoleRowId, @DiagnosticsInfrastructureFunctionRowId, @DateTimeUTC, @Line, @MDRESULT, @ErrorCode, @ErrorCodeMessage, @InstanceNumber, @EventLevel, @Message)", d);
        }

        public int GetInfrastructureFunctionRowId(String functionName)
        {
            // Populate the cache tables
            if (_infaFunction == null)
                PopulateCacheTableInfraFunction();

            // Find if the value is in the cache table
            var x = from a in _infaFunction.AsEnumerable()
                    where a.Field<string>("FunctionName") == functionName
                    select a;

            if (x.Count() == 0)
            {
                // Insert a new Entry
                _sql.RunQueryGetRowsAffected("INSERT INTO DiagnosticsInfrastructureFunction (FunctionName) VALUES (@FunctionName)",
                    SqlHelper.GenerateParameterDictionary("@FunctionName", functionName));

                // Re-populate Cache
                PopulateCacheTableInfraFunction();

                // Recurse this bitch
                return GetInfrastructureFunctionRowId(functionName);
            }
            else
            {
                return x.First().Field<int>("DiagnosticsInfrastructureFunctionRowId");
            }
        }

        #endregion


        #region Performance Counters

        private void PopulateCacheTableRole()
        {
            _roleCache = _sql.RunQueryGetDataTable("SELECT * FROM [Role]");
        }


        private void PopulateCacheTablePerformanceCounter()
        {
            _performanceCounterCache = _sql.RunQueryGetDataTable("SELECT * FROM PerformanceCounter");
        }

        public void WritePerformanceCounter(PerformanceCounterEntity perf)
        {
            _sql.RunQueryGetRowsAffected("INSERT INTO [PerformanceSample] (PerformanceCounterRowId,RoleRowId,DateTimeUTC,InstanceNumber,SampleValue) VALUES (@PerformanceCounterRowId,@RoleRowId,@DateTimeUTC,@InstanceNumber,@SampleValue)",
                SqlHelper.GenerateParameterDictionary("@PerformanceCounterRowId", GetPerformanceCounterId(perf.CounterName), "@RoleRowId", GetRoleRowId(Guid.Parse(perf.DeploymentId), perf.Role), "@DateTimeUTC", new DateTime(perf.EventTickCount), "@InstanceNumber", GetInstanceNumberFromFullName(perf.RoleInstance), "@SampleValue", perf.CounterValue));
        }

        public void WritePerformanceCounterBulk(DataTable data)
        {
            _sql.RunBulkInsert("PerformanceSample", data, true);
        }

        public int GetInstanceNumberFromFullName(String instanceName)
        {
            String[] s = instanceName.Split(new String[] { "_" }, StringSplitOptions.RemoveEmptyEntries);
            return int.Parse(s[s.Length - 1]);
        }

        public int GetRoleRowId(Guid deploymentId, String roleName)
        {
            // Populate cache tables if null
            if (_roleCache == null)
                PopulateCacheTableRole();

            var x = from a in _roleCache.AsEnumerable()
                    where a.Field<Guid>("DeploymentId") == deploymentId
                        && a.Field<String>("RoleName") == roleName
                    select a;

            if (x.Count() == 0)
            {
                // Insert a new Entry
                _sql.RunQueryGetRowsAffected("INSERT INTO [Role] (DeploymentId,RoleName) VALUES (@DeploymentId,@RoleName)",
                    SqlHelper.GenerateParameterDictionary("@DeploymentId", deploymentId, "@RoleName", roleName));

                // Re-populate Cache
                PopulateCacheTableRole();

                // Recurse this bitch
                return GetRoleRowId(deploymentId, roleName);
            }
            else
            {
                return x.First().Field<int>("RoleRowId");
            }
        }

        public int GetPerformanceCounterId(String performanceCounterString)
        {
            // Populate the cache tables
            if (_performanceCounterCache == null)
                PopulateCacheTablePerformanceCounter();

            // Find if the value is in the cache table
            var x = from a in _performanceCounterCache.AsEnumerable()
                    where a.Field<string>("FullCounterString") == performanceCounterString
                    select a;

            if (x.Count() == 0)
            {
                String[] s = performanceCounterString.Split(new String[] { @"\" }, StringSplitOptions.RemoveEmptyEntries);

                String counterObjectName = s[0];
                String counterCounterName = performanceCounterString.Replace(@"\" + counterObjectName + @"\", "");
                String counterInstanceName = "";

                if (counterObjectName.Contains(@"("))
                {
                    String[] r = counterObjectName.Split(new String[] { "(", ")" }, StringSplitOptions.RemoveEmptyEntries);

                    // update the counter instance
                    counterInstanceName = r[r.Length - 1];

                    //remove the instance from the counter Object Name
                    counterObjectName = r[0];
                }


                // Insert a new Entry
                _sql.RunQueryGetRowsAffected("INSERT INTO PerformanceCounter (ObjectName,CounterName,InstanceName,FullCounterString) VALUES (@ObjectName,@CounterName,@InstanceName,@FullCounterString)",
                    SqlHelper.GenerateParameterDictionary("@ObjectName", counterObjectName, "@CounterName", counterCounterName, "@InstanceName", counterInstanceName, "@FullCounterString", performanceCounterString));

                // Re-populate Cache
                PopulateCacheTablePerformanceCounter();

                // Recurse this bitch
                return GetPerformanceCounterId(performanceCounterString);
            }
            else
            {
                return x.First().Field<int>("PerformanceCounterRowId");
            }
        }

        #endregion

        #region Tables

        #region Get - Tables

        public GenericTableEntity GetTableObjectMetadata(Guid storageAccountId, String tableName)
        {
            DataTable d = GetTableDownloadsForTable(storageAccountId, tableName);

            GenericTableEntity t = new GenericTableEntity();

            if (d.Rows.Count == 1)
            {
                // Single row, make an update
                t.TableName = d.Rows[0]["TableName"].ToString();
                t.PartitionKey = d.Rows[0]["LastProcessedPartitionKey"].ToString();
                t.RowKey = d.Rows[0]["LastProcessedRowKey"].ToString();
                t.Timestamp = (DateTime)d.Rows[0]["LastProcessedTimestampUTC"];
            }
            else
            {
                // Create a new entry
                int o = -90;

                try {
                    if (ConfigurationManager.AppSettings["FirstDataStartingDateOffset"] != String.Empty)
                    {
                        o = Int32.Parse(ConfigurationManager.AppSettings["FirstDataStartingDateOffset"]);
                    }
                } catch (Exception ex)
                {
                    Logger.WriteMessage(String.Format("Failed to convert configuration value for 'FirstDataStartingDateOffset' to int, default value of {0} will be used.  EXCEPTION: {1}",o,ex.ToString()), EventLevel.Warning,2015);
                }

                t.PartitionKey = "0" + (DateTime.UtcNow.AddDays(o)).Ticks.ToString();
                t.RowKey = "";
                t.TableName = tableName;
                t.Timestamp = DateTime.UtcNow.AddDays(o);
            }

            // Return the single result
            return t;
        }

        #endregion Get - Tables


        #region Set - Tables
        public void UpdateTableObjectUpdate(Guid storageAccountId, String tableName, String lastProcessedPartitionKey, String lastProcessedRowKey, DateTime lastProcessedTimestampUTC)
        {
            DataTable d = GetTableDownloadsForTable(storageAccountId, tableName);

            if (d.Rows.Count == 1)
            {
                // Perform an update
                _sql.RunQueryGetRowsAffected("UPDATE TableDownload SET DateTimeLastUpdatedUTC = @DateTimeLastUpdatedUTC, LastProcessedPartitionKey = @LastProcessedPartitionKey, LastProcessedRowKey = @LastProcessedRowKey, LastProcessedTimestampUTC = @LastProcessedTimestampUTC WHERE TableDownloadId = @TableDownloadId",
                    SqlHelper.GenerateParameterDictionary("@DateTimeLastUpdatedUTC", DateTime.UtcNow, "@LastProcessedPartitionKey", lastProcessedPartitionKey, "@LastProcessedRowKey", lastProcessedRowKey, "@TableDownloadId", d.Rows[0]["TableDownloadId"], "@LastProcessedTimestampUTC", lastProcessedTimestampUTC));

            }
            else
            {
                if (d.Rows.Count > 1)
                {
                    // Delete all existing rows
                    _sql.RunQueryGetRowsAffected("DELETE FROM TableDownload WHERE StorageAccountId = @StorageAccountId AND TableName = @TableName",
                        SqlHelper.GenerateParameterDictionary("@StorageAccountId", storageAccountId, "@TableName", tableName));
                }

                // Insert a new row
                _sql.RunQueryGetRowsAffected("INSERT INTO [TableDownload] (StorageAccountId,TableName,DateTimeLastUpdatedUTC,LastProcessedPartitionKey,LastProcessedRowKey,LastProcessedTimestampUTC) VALUES (@StorageAccountId,@TableName,@DateTimeLastUpdatedUTC,@LastProcessedPartitionKey,@LastProcessedRowKey,@LastProcessedTimestampUTC)",
                    SqlHelper.GenerateParameterDictionary("@StorageAccountId", storageAccountId, "@TableName", tableName, "@DateTimeLastUpdatedUTC", DateTime.UtcNow, "@LastProcessedPartitionKey", lastProcessedPartitionKey, "@LastProcessedRowKey", lastProcessedRowKey, "@LastProcessedTimestampUTC", lastProcessedTimestampUTC));
            }

            
            
        }
        #endregion Set - Tables

        #region Private - Tables

        private DataTable GetTableDownloadsForTable(Guid storageAccountId, String tableName)
        {
            return _sql.RunQueryGetDataTable("SELECT * FROM TableDownload WHERE StorageAccountId = @StorageAccountId and TableName = @TableName", SqlHelper.GenerateParameterDictionary("@StorageAccountId", storageAccountId, "@TableName", tableName));
        }

        #endregion Private - Tables


        #endregion

        #region Die
        public void Dispose()
        {
            try
            {
                _sql.Dispose();
            }
            catch (Exception) { } // ignore
        }
        #endregion
    }

    public struct GenericTableEntity
    {
        public String TableName;
        public String PartitionKey;
        public String RowKey;
        public DateTime Timestamp;
    }
}
